﻿using System;
using System.Collections.ObjectModel;
using Oracle.DataAccess.Client;

namespace ISHotel.Database
{
    [Serializable]
    public class SluzbaTable : DbTable
    {
        public static String TABLE_NAME = "Sluzba";

        public String SQL_SELECT = "SELECT * FROM Sluzba ORDER BY id_sluzby ASC";
        public String SQL_SELECT_id_sluzby = "SELECT * FROM Sluzba WHERE id_sluzby=:id_sluzby";
        public String SQL_INSERT = "INSERT INTO Sluzba (nazev_sluzby, popis_sluzby, cena_sluzby) VALUES (:nazev_sluzby, :popis_sluzby, :cena_sluzby)";
        public String SQL_UPDATE = "UPDATE Sluzba SET nazev_sluzby=:nazev_sluzby, popis_sluzby=:popis_sluzby, cena_sluzby=:cena_sluzby WHERE id_sluzby=:id_sluzby";
        public String SQL_DELETE = "DELETE FROM Sluzba WHERE id_sluzby=:id_sluzby";

        public SluzbaTable()
            : base(TABLE_NAME)
        {
        }

                /**
         * Insert the record.
         **/
        public int Insert(Sluzba Sluzba)
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_INSERT);
            PrepareCommand(command, Sluzba);
            int ret = mDatabase.Insert(command);
            mDatabase.Close();
            return ret;
        }

        /**
         * Update the record.
         **/
        public int Update(Sluzba Sluzba)
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_UPDATE);
            
            command.Parameters.Add(new OracleParameter(":" + Sluzba.ATTR_nazev_sluzby, OracleDbType.Varchar2, Sluzba.nazev_sluzby.Length));
            command.Parameters[":" + Sluzba.ATTR_nazev_sluzby].Value = Sluzba.nazev_sluzby;

            if (Sluzba.popis_sluzby != null)
            {
                command.Parameters.Add(new OracleParameter(":" + Sluzba.ATTR_popis_sluzby, OracleDbType.Varchar2, Sluzba.popis_sluzby.Length));
                command.Parameters[":" + Sluzba.ATTR_popis_sluzby].Value = Sluzba.popis_sluzby;
            }
            else
            {
                command.Parameters.Add(new OracleParameter(":" + Sluzba.ATTR_popis_sluzby, OracleDbType.Varchar2, 15));
                command.Parameters[":" + Sluzba.ATTR_popis_sluzby].Value = "NOT PROVIDED";
            }

            command.Parameters.Add(new OracleParameter(":" + Sluzba.ATTR_cena_sluzby, OracleDbType.Decimal));
            command.Parameters[":" + Sluzba.ATTR_cena_sluzby].Value = Sluzba.cena_sluzby;

            command.Parameters.Add(new OracleParameter(":" + Sluzba.ATTR_id_sluzby, OracleDbType.Decimal));
            command.Parameters[":" + Sluzba.ATTR_id_sluzby].Value = Sluzba.id_sluzby;

            int ret = mDatabase.Update(command);
            mDatabase.Close();
            return ret;
        }

        /**
         * Prepare a command.
         **/

        private void PrepareCommand(OracleCommand command, Sluzba Sluzba)
        {
            command.Parameters.Add(new OracleParameter(":" + Sluzba.ATTR_nazev_sluzby, OracleDbType.Varchar2, Sluzba.nazev_sluzby.Length));
            command.Parameters[":" + Sluzba.ATTR_nazev_sluzby].Value = Sluzba.nazev_sluzby;

            if (Sluzba.popis_sluzby != null)
            {
                command.Parameters.Add(new OracleParameter(":" + Sluzba.ATTR_popis_sluzby, OracleDbType.Varchar2, Sluzba.popis_sluzby.Length));
                command.Parameters[":" + Sluzba.ATTR_popis_sluzby].Value = Sluzba.popis_sluzby;
            }
            else
            {
                command.Parameters.Add(new OracleParameter(":" + Sluzba.ATTR_popis_sluzby, OracleDbType.Varchar2, 15));
                command.Parameters[":" + Sluzba.ATTR_popis_sluzby].Value = "NOT PROVIDED";
            }

            command.Parameters.Add(new OracleParameter(":" + Sluzba.ATTR_cena_sluzby, OracleDbType.Decimal));
            command.Parameters[":" + Sluzba.ATTR_cena_sluzby].Value = Sluzba.cena_sluzby;
            }

        /**
         * Select records.
         **/
        public Collection<Sluzba> Select()
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_SELECT);
            OracleDataReader reader = mDatabase.Select(command);

            Collection<Sluzba> Sluzbas = Read(reader);
            reader.Close();
            mDatabase.Close();
            return Sluzbas;
        }

        /**
         * Select the record.
         **/
        public Sluzba Select(int id_sluzby)
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_SELECT_id_sluzby);

            command.Parameters.Add(new OracleParameter(":" + Sluzba.ATTR_id_sluzby, OracleDbType.Varchar2));
            command.Parameters[":" + Sluzba.ATTR_id_sluzby].Value = id_sluzby;
            OracleDataReader reader = mDatabase.Select(command);

            Collection<Sluzba> Sluzbas = Read(reader);
            Sluzba Sluzba1 = null;
            if (Sluzbas.Count == 1)
            {
                Sluzba1 = Sluzbas[0];
            }
            reader.Close();
            mDatabase.Close();
            return Sluzba1;
        }

        private Collection<Sluzba> Read(OracleDataReader reader)
        {
            Collection<Sluzba> Sluzbas = new Collection<Sluzba>();

            while (reader.Read())
            {
                Sluzba Sluzba = new Sluzba();
                Sluzba.id_sluzby = (int)reader.GetDecimal(0);
                Sluzba.nazev_sluzby = reader.GetString(1);
                if (!reader.IsDBNull(2))
                    if (reader.GetString(2) != "NOT PROVIDED")
                        Sluzba.popis_sluzby = reader.GetString(2);
                Sluzba.cena_sluzby = (int)reader.GetDecimal(3);
                Sluzbas.Add(Sluzba);
            }
            return Sluzbas;
        }

        /**
         * Delete the record.
         */
        public int Delete(int id_sluzby)
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_DELETE);

            command.Parameters.Add(new OracleParameter(":" + Sluzba.ATTR_id_sluzby, OracleDbType.Decimal));
            command.Parameters[":" + Sluzba.ATTR_id_sluzby].Value = id_sluzby;
            int ret;
            try
            {
                ret = mDatabase.Delete(command);
            }
            catch (Exception e)
            {
                throw e;
            }
            mDatabase.Close();
            return ret;
        }
    }
}